--- Example of geographically weigthed social class of 13 nearest neighbors using Gaussian distance decay
--AGES 11 EXAMPLE

--1 Join Individuals and addresses for each year
SELECT DISTINCT
LandskronaChildren.Id_I, 
LandskronaChildren.HouseholdNo, 
LandskronaChildren.FamilyNumber, 
LandskronaChildren.DateInc2 as DateInc, 
LandskronaChildren.HF_HC1, 
LandskronaChildren.HF_HC2, 
LandskronaChildren.HF_HC3, 
LandskronaChildren.HF_HC4, 
LandskronaChildren.AddressId, 
dbo.hedfin_NearMatris.NearAddress, 
dbo.hedfin_NearMatris.NearDist, 
dbo.hedfin_NearMatris.NEAR_RANK
INTO Step1_age11
FROM LandskronaChildren LEFT OUTER JOIN dbo.hedfin_NearMatris ON LandskronaChildren.AddressId = dbo.hedfin_NearMatris.AddressID
WHERE (agecat = 11)

			
--2 Join individual matrix and nearby individuals (for 11 year old persons and 10-12 years neighbours)
SELECT   DISTINCT     
Step1_age11.*, 
LandskronaChildren.Id_I AS NearId_I, 
LandskronaChildren.HouseholdNo as NearHouseholdNo, 
LandskronaChildren.FamilyNumber AS NearFamilyNumber, 
LandskronaChildren.agecat as NearAgecat, 
LandskronaChildren.HF_HC1 AS NearHF_HC1, 
LandskronaChildren.HF_HC2 AS NearHF_HC2, 
LandskronaChildren.HF_HC3 AS NearHF_HC3, 
LandskronaChildren.HF_HC4 AS NearHF_HC4
INTO Step1_age11_1
FROM Step1_age11 INNER JOIN LandskronaChildren ON Step1_age11.NearAddress = LandskronaChildren.AddressId AND Step1_age11.DateInc = LandskronaChildren.DateInc
WHERE (LandskronaChildren.agecat = 10 or LandskronaChildren.agecat = 11 or LandskronaChildren.agecat = 12)
ORDER BY NearDist

 --3 Rank the neighbours (aged 10-12) of the children aged 11. If ties exist, random ones are selected
 ----REMOVE SELF-NEIGHBOURS
SELECT *
INTO Step1_age11_2
FROM Step1_age11_1
WHERE ID_I IS NOT NULL AND ID_I <> NearID_I AND HouseholdNo <> NearHouseholdNo AND Familynumber <> NearFamilyNumber AND NearID_I IS NOT NULL
ORDER BY ID_I

;WITH cte AS
(
SELECT *,
ROW_NUMBER() OVER (PARTITION BY Id_I, DateInc ORDER BY NearDist ASC) AS rn
FROM Step1_age11_2
)
SELECT *
INTO Step1_age11_2rank
FROM cte
ORDER BY ID_I, DateInc

----4 Weight the social class of the 13 neighbors, Gaussian function

---4a Summarize the weights of the neighbours aged 10-12, adaptive bandwidth
SELECT  
ID_I, 
DateInc, 
MAX(NearDist) AS MaxNearDist
INTO MaxNearDist
FROM Step1_age11_2rank
WHERE rn <= 13
GROUP BY ID_I, DateInc
GO

UPDATE MaxNearDist SET MaxNearDist = 1 WHERE MaxNearDist = 0 

SELECT Step1_age11_2rank.Id_I, Step1_age11_2rank.DateInc,
SUM(ISNULL(EXP(-(0.5*POWER(NearDist/MaxNearDist, 2))), 0)) AS SUMWeight,
MaxNearDist
INTO SumWeight13Ad
FROM Step1_age11_2rank INNER JOIN MaxNearDist ON Step1_age11_2rank.Id_I = MaxNearDist.ID_I AND Step1_age11_2rank.DateInc = MaxNearDist.DateInc
WHERE (Step1_age11_2rank.rn <= 13)
GROUP BY Step1_age11_2rank.Id_I, Step1_age11_2rank.DateInc, MaxNearDist.MaxNearDist

---4b Join summarized weight to distance matrix
SELECT DISTINCT Step1_age11_2rank.*, SumWeight13Ad.SUMWeight,
MaxNearDist
INTO Step1_age11_2rank_W_13
FROM Step1_age11_2rank LEFT OUTER JOIN SumWeight13Ad ON Step1_age11_2rank.Id_I = SumWeight13Ad.ID_I AND Step1_age11_2rank.DateInc = SumWeight13Ad.DateInc WHERE rn <= 13

		
--- 4c Compute weighted class share, Gaussian function, adaptvie bandwidth
ALTER TABLE Step1_age11_2rank_W_13
ADD W13_HFHC1 float, W13_HFHC2 float, W13_HFHC3 float, W13_HFHC4 float;
GO

UPDATE Step1_age11_2rank_W_13
SET W13_HFHC1 = (ISNULL(EXP(-0.5*(POWER(NearDist/MaxNearDist, 2))), 0)/SUMWeight)

ALTER TABLE Step1_age11_2rank_W_13
ADD nearHFHC1_w13a float, nearHFHC2_w13a float, nearHFHC3_w13a float, nearHFHC4_w13a float;
GO

SELECT ID_I, DateInc, 
SUM(W13_HFHC1 * nearHF_HC1) AS nearHFHC1_w13a, 
SUM(W13_HFHC1 * nearHF_HC2) AS nearHFHC2_w13a, 
SUM(W13_HFHC1 * nearHF_HC3) AS nearHFHC3_w13a, 
SUM(W13_HFHC1 * nearHF_HC4) AS nearHFHC4_w13a
INTO NearGWShare13
FROM Step1_age11_2rank_W_13
GROUP BY ID_I, DateInc
GO

UPDATE Step1_age11_2rank_W_13
SET Step1_age11_2rank_W_13.nearHFHC1_w13a = NearGWShare13.nearHFHC1_w13a,
Step1_age11_2rank_W_13.nearHFHC2_w13a = NearGWShare13.nearHFHC2_w13a,
Step1_age11_2rank_W_13.nearHFHC3_w13a = NearGWShare13.nearHFHC3_w13a,
Step1_age11_2rank_W_13.nearHFHC4_w13a = NearGWShare13.nearHFHC4_w13a
FROM  Step1_age11_2rank_W_13 INNER JOIN NearGWShare13 ON Step1_age11_2rank_W_13.Id_I = NearGWShare13.ID_I AND Step1_age11_2rank_W_13.DateInc = NearGWShare13.DateInc
GO

----GW Share of each social class for each child aged 11
SELECT DISTINCT ID_I as Id_I, 
year(DateInc) as year, 
AddressID as AddressId, 
nearHFHC1_w13a as nearHFHC1_11_13,
 nearHFHC2_w13a as nearHFHC2_11_13, 
 nearHFHC3_w13a as nearHFHC3_11_13, 
 nearHFHC4_w13a as nearHFHC4_11_13
FROM Step1_age11_2rank_W_13
ORDER BY ID_I, year
